#!/bin/sh
#######hive数仓小项目
####创建日期：2025-04-30
####创建人员：2502全体
###版本功能：加工dm层指标，计算各地区各产品排名，以及销售总额
###来源表：dw_cust_info_02、dw_product_info_02、dw_addr_info_02
###落地表：dm_rk_02
###开始编写脚本
hive -v -e "set hive.exec.mode.local.auto=true;
insert overwrite table dm02.dm_rk_02
select a.*,
        row_number() over(partition by addrname order by s_amt desc) r_n,
        current_timestamp etl_dt
        from
(select 
    nvl(b.addrname,'其他地区') addrname,
    nvl(c.product_name ,'其他产品') product_name,
    sum(a.txt_amt) s_amt
    from dw02.dw_cust_info_02 a left join 
--关联之前对从表的全字段进行去重，防止数据发散
(
    select 
        addrno,
        addrname,
        addrcode
        from dw02.dw_addr_info_02
        group by 
        addrno,
        addrname,
        addrcode
        order by
        addrno,
        addrname,
        addrcode
) b on a.addrno=b.addrno
        left join 
        (select 
                  product_code, 
                  product_name, 
                  product_type
        from dw02.dw_product_info_02
                    group by 
                  product_code, 
                  product_name, 
                  product_type 
                  order  by 
                  product_code, 
                  product_name, 
                  product_type 
        ) c on a.product_code=c.product_code
        group by 
    nvl(b.addrname,'其他地区'),
    nvl(c.product_name,'其他产品')
    ) a;"
echo $?
echo "数据已计算完成，请验证数据"

